import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from db.db_util import DBUtil
from entities.business import Business

class BusinessDao:
    def login(self, business_id, password):
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = "SELECT * FROM business WHERE businessId = %s AND password = %s"
            cursor.execute(sql, (business_id, password))
            result = cursor.fetchone()
            if result:
                return Business(
                    business_id=result['businessId'],
                    password=result['password'],
                    business_name=result['businessName'],
                    business_address=result['businessAddress'],
                    business_explain=result['businessExplain'],
                    star_price=float(result['starPrice']),
                    delivery_price=float(result['deliveryPrice'])
                )
            return None
        finally:
            DBUtil.close(conn, cursor)
    
        
    def get_by_id(self, business_id):
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = "SELECT * FROM business WHERE businessId = %s"
            cursor.execute(sql, (business_id,))
            result = cursor.fetchone()
            if result:
                return Business(
                    business_id=result['businessId'],
                    password=result['password'],
                    business_name=result['businessName'],
                    business_address=result['businessAddress'],
                    business_explain=result['businessExplain'],
                    star_price=float(result['starPrice']),
                    delivery_price=float(result['deliveryPrice'])
                )
            return None
        finally:
            DBUtil.close(conn, cursor)

    def list_all(self):
        """
        获取所有商家列表
        :return: 商家对象列表
        """
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = "SELECT * FROM business"
            cursor.execute(sql)
            results = cursor.fetchall()
            
            businesses = []
            for result in results:
                businesses.append(Business(
                    business_id=result['businessId'],
                    password=result['password'],
                    business_name=result['businessName'],
                    business_address=result['businessAddress'],
                    business_explain=result['businessExplain'],
                    star_price=float(result['starPrice']),
                    delivery_price=float(result['deliveryPrice'])
                ))
            return businesses
        finally:
            DBUtil.close(conn, cursor)

    def update(self, business):
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = """UPDATE business SET 
                     password=%s, businessName=%s, businessAddress=%s, 
                     businessExplain=%s, starPrice=%s, deliveryPrice=%s 
                     WHERE businessId=%s"""
            cursor.execute(sql, (
                business.password,
                business.business_name,
                business.business_address,
                business.business_explain,
                business.star_price,
                business.delivery_price,
                business.business_id
            ))
            conn.commit()
            return cursor.rowcount
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            DBUtil.close(conn, cursor)

    def search_by_name(self, name):
        """
        按商家名称搜索商家
        :param name: 搜索关键词
        :return: 匹配的商家列表
        """
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = "SELECT * FROM business WHERE businessName LIKE %s"
            cursor.execute(sql, (f"%{name}%",))
            results = cursor.fetchall()
            
            businesses = []
            for result in results:
                businesses.append(Business(
                    business_id=result['businessId'],
                    password=result['password'],
                    business_name=result['businessName'],
                    business_address=result['businessAddress'],
                    business_explain=result['businessExplain'],
                    star_price=float(result['starPrice']),
                    delivery_price=float(result['deliveryPrice'])
                ))
            return businesses
        finally:
            DBUtil.close(conn, cursor)

    def create(self, business):
        """
        创建新商家
        :param business: Business对象
        :return: 新创建的商家ID
        """
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            sql = """INSERT INTO business 
                    (password, businessName, businessAddress, 
                    businessExplain, starPrice, deliveryPrice) 
                    VALUES (%s, %s, %s, %s, %s, %s)"""
            cursor.execute(sql, (
                business.password,
                business.business_name,
                business.business_address,
                business.business_explain,
                business.star_price,
                business.delivery_price
            ))
            conn.commit()
            return cursor.lastrowid
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            DBUtil.close(conn, cursor)

    def delete(self, business_id):
        """
        删除商家
        :param business_id: 商家ID
        :return: 影响的行数
        """
        conn = None
        cursor = None
        try:
            conn = DBUtil.get_connection()
            cursor = conn.cursor()
            
            # 先删除关联的食品
            cursor.execute("DELETE FROM food WHERE businessId = %s", (business_id,))
            
            # 再删除商家
            sql = "DELETE FROM business WHERE businessId = %s"
            cursor.execute(sql, (business_id,))
            conn.commit()
            return cursor.rowcount
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            DBUtil.close(conn, cursor)

    def update(self, business):
            """更新商家信息"""
            conn = None
            cursor = None
            try:
                conn = DBUtil.get_connection()
                cursor = conn.cursor()
                sql = """UPDATE business SET 
                        password=%s, businessName=%s, businessAddress=%s, 
                        businessExplain=%s, starPrice=%s, deliveryPrice=%s 
                        WHERE businessId=%s"""
                cursor.execute(sql, (
                    business.password,
                    business.business_name,
                    business.business_address,
                    business.business_explain,
                    business.star_price,
                    business.delivery_price,
                    business.business_id
                ))
                conn.commit()
                return cursor.rowcount
            except Exception as e:
                conn.rollback()
                raise e
            finally:
                DBUtil.close(conn, cursor)